alter table spmi_dm.dm_composite_bill_network_sign_daliy_summary_dt
    set tblproperties ('external.table.purge' = 'true');
drop table spmi_dm.dm_composite_bill_network_sign_daliy_summary_dt;
CREATE EXTERNAL TABLE spmi_dm.dm_composite_bill_network_sign_daliy_summary_dt
(
    sign_date                  string COMMENT '签收日期(年月日)',
    network_id                 string COMMENT '寄件网点id',
    network_code               string COMMENT '寄件网点编码',
    network_name               string COMMENT '寄件网点名称',
    send_fran_id               string COMMENT '寄件加盟商id',
    send_fran_code             string COMMENT '寄件加盟商code',
    send_fran_name             string COMMENT '寄件加盟商名称',
    send_agent_id              string COMMENT '寄件加盟商名称',
    send_agent_code            string COMMENT '寄件加盟商名称',
    send_agent_name            string COMMENT '寄件加盟商名称',
    network_type               string COMMENT '寄件网点-网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
    send_financial_center_id   string COMMENT '寄件财务中心id',
    send_financial_center_code string COMMENT '寄件财务中心编码',
    send_financial_center_name string COMMENT '寄件财务中心名称',
    receiver_province_id       string COMMENT '收件省份id',
    receiver_province_name     string COMMENT '收件省份名称',
    weight_one_count           decimal(12, 2) COMMENT '件数公斤段0',
    weight_two_count           decimal(12, 2) COMMENT '件数公斤段0-0.5',
    weight_three_count         decimal(12, 2) COMMENT '件数公斤段0.5-1',
    weight_four_count          decimal(12, 2) COMMENT '件数公斤段1-2',
    weight_five_count          decimal(12, 2) COMMENT '件数公斤段2-2.5',
    weight_six_count           decimal(12, 2) COMMENT '件数公斤段2.5-3.2',
    weight_seven_count         decimal(12, 2) COMMENT '件数公斤段3.2-5.2',
    weight_eight_count         decimal(12, 2) COMMENT '件数公斤段5.2-10.3',
    weight_nine_count          decimal(12, 2) COMMENT '件数公斤段10.3-20',
    weight_ten_count           decimal(12, 2) COMMENT '件数公斤段20-30',
    weight_eleven_count        decimal(12, 2) COMMENT '件数公斤段30+',
    weight_twelve_count        decimal(12, 2) COMMENT '件数公斤段30++',
    weight_one_weight          decimal(12, 2) COMMENT '重量公斤段0',
    weight_two_weight          decimal(12, 2) COMMENT '重量公斤段0-0.5',
    weight_three_weight        decimal(12, 2) COMMENT '重量公斤段0.5-1',
    weight_four_weight         decimal(12, 2) COMMENT '重量公斤段1-2',
    weight_five_weight         decimal(12, 2) COMMENT '重量公斤段2-2.5',
    weight_six_weight          decimal(12, 2) COMMENT '重量公斤段2.5-3.2',
    weight_seven_weight        decimal(12, 2) COMMENT '重量公斤段3.2-5.2',
    weight_eight_weight        decimal(12, 2) COMMENT '重量公斤段5.2-10.3',
    weight_nine_weight         decimal(12, 2) COMMENT '重量公斤段10.3-20',
    weight_ten_weight          decimal(12, 2) COMMENT '重量公斤段20-30',
    weight_eleven_weight       decimal(12, 2) COMMENT '重量公斤段30+',
    weight_twelve_weight       decimal(12, 2) COMMENT '重量公斤段30++',
    weight_one_composite       decimal(12, 2) COMMENT '一口价公斤段0',
    weight_two_composite       decimal(12, 2) COMMENT '一口价公斤段0-0.5',
    weight_three_composite     decimal(12, 2) COMMENT '一口价公斤段0.5-1',
    weight_four_composite      decimal(12, 2) COMMENT '一口价公斤段1-2',
    weight_five_composite      decimal(12, 2) COMMENT '一口价公斤段2-2.5',
    weight_six_composite       decimal(12, 2) COMMENT '一口价公斤段2.5-3.2',
    weight_seven_composite     decimal(12, 2) COMMENT '一口价公斤段3.2-5.2',
    weight_eight_composite     decimal(12, 2) COMMENT '一口价公斤段5.2-10.3',
    weight_nine_composite      decimal(12, 2) COMMENT '一口价公斤段10.3-20',
    weight_ten_composite       decimal(12, 2) COMMENT '一口价公斤段20-30',
    weight_eleven_composite    decimal(12, 2) COMMENT '一口价公斤段30+',
    weight_twelve_composite    decimal(12, 2) COMMENT '一口价公斤段30++',
    weight_one_tof             decimal(12, 2) COMMENT '中转费公斤段0',
    weight_two_tof             decimal(12, 2) COMMENT '中转费公斤段0-0.5',
    weight_three_tof           decimal(12, 2) COMMENT '中转费公斤段0.5-1',
    weight_four_tof            decimal(12, 2) COMMENT '中转费公斤段1-2',
    weight_five_tof            decimal(12, 2) COMMENT '中转费公斤段2-2.5',
    weight_six_tof             decimal(12, 2) COMMENT '中转费公斤段2.5-3.2',
    weight_seven_tof           decimal(12, 2) COMMENT '中转费公斤段3.2-5.2',
    weight_eight_tof           decimal(12, 2) COMMENT '中转费公斤段5.2-10.3',
    weight_nine_tof            decimal(12, 2) COMMENT '中转费公斤段10.3-20',
    weight_ten_tof             decimal(12, 2) COMMENT '中转费公斤段20-30',
    weight_eleven_tof          decimal(12, 2) COMMENT '中转费公斤段30+',
    weight_twelve_tof          decimal(12, 2) COMMENT '中转费公斤段30++',
    weight_one_opt             decimal(12, 2) COMMENT '操作费公斤段0',
    weight_two_opt             decimal(12, 2) COMMENT '操作费公斤段0-0.5',
    weight_three_opt           decimal(12, 2) COMMENT '操作费公斤段0.5-1',
    weight_four_opt            decimal(12, 2) COMMENT '操作费公斤段1-2',
    weight_five_opt            decimal(12, 2) COMMENT '操作费公斤段2-2.5',
    weight_six_opt             decimal(12, 2) COMMENT '操作费公斤段2.5-3.2',
    weight_seven_opt           decimal(12, 2) COMMENT '操作费公斤段3.2-5.2',
    weight_eight_opt           decimal(12, 2) COMMENT '操作费公斤段5.2-10.3',
    weight_nine_opt            decimal(12, 2) COMMENT '操作费公斤段10.3-20',
    weight_ten_opt             decimal(12, 2) COMMENT '操作费公斤段20-30',
    weight_eleven_opt          decimal(12, 2) COMMENT '操作费公斤段30+',
    weight_twelve_opt          decimal(12, 2) COMMENT '操作费公斤段30++',
    weight_one_piece           decimal(12, 2) COMMENT '派件费公斤段0',
    weight_two_piece           decimal(12, 2) COMMENT '派件费公斤段0-0.5',
    weight_three_piece         decimal(12, 2) COMMENT '派件费公斤段0.5-1',
    weight_four_piece          decimal(12, 2) COMMENT '派件费公斤段1-2',
    weight_five_piece          decimal(12, 2) COMMENT '派件费公斤段2-2.5',
    weight_six_piece           decimal(12, 2) COMMENT '派件费公斤段2.5-3.2',
    weight_seven_piece         decimal(12, 2) COMMENT '派件费公斤段3.2-5.2',
    weight_eight_piece         decimal(12, 2) COMMENT '派件费公斤段5.2-10.3',
    weight_nine_piece          decimal(12, 2) COMMENT '派件费公斤段10.3-20',
    weight_ten_piece           decimal(12, 2) COMMENT '派件费公斤段20-30',
    weight_eleven_piece        decimal(12, 2) COMMENT '派件费公斤段30+',
    weight_twelve_piece        decimal(12, 2) COMMENT '派件费公斤段30++',
    weight_one_base_piece      decimal(12, 2) COMMENT '基础派费公斤段0',
    weight_two_base_piece      decimal(12, 2) COMMENT '基础派费公斤段0-0.5',
    weight_three_base_piece    decimal(12, 2) COMMENT '基础派费公斤段0.5-1',
    weight_four_base_piece     decimal(12, 2) COMMENT '基础派费公斤段1-2',
    weight_five_base_piece     decimal(12, 2) COMMENT '基础派费公斤段2-2.5',
    weight_six_base_piece      decimal(12, 2) COMMENT '基础派费公斤段2.5-3.2',
    weight_seven_base_piece    decimal(12, 2) COMMENT '基础派费公斤段3.2-5.2',
    weight_eight_base_piece    decimal(12, 2) COMMENT '基础派费公斤段5.2-10.3',
    weight_nine_base_piece     decimal(12, 2) COMMENT '基础派费公斤段10.3-20',
    weight_ten_base_piece      decimal(12, 2) COMMENT '基础派费公斤段20-30',
    weight_eleven_base_piece   decimal(12, 2) COMMENT '基础派费公斤段30+',
    weight_twelve_base_piece   decimal(12, 2) COMMENT '基础派费公斤段30++',
    weight_one_face            decimal(12, 2) COMMENT '面单费公斤段0',
    weight_two_face            decimal(12, 2) COMMENT '面单费公斤段0-0.5',
    weight_three_face          decimal(12, 2) COMMENT '面单费公斤段0.5-1',
    weight_four_face           decimal(12, 2) COMMENT '面单费公斤段1-2',
    weight_five_face           decimal(12, 2) COMMENT '面单费公斤段2-2.5',
    weight_six_face            decimal(12, 2) COMMENT '面单费公斤段2.5-3.2',
    weight_seven_face          decimal(12, 2) COMMENT '面单费公斤段3.2-5.2',
    weight_eight_face          decimal(12, 2) COMMENT '面单费公斤段5.2-10.3',
    weight_nine_face           decimal(12, 2) COMMENT '面单费公斤段10.3-20',
    weight_ten_face            decimal(12, 2) COMMENT '面单费公斤段20-30',
    weight_eleven_face         decimal(12, 2) COMMENT '面单费公斤段30+',
    weight_twelve_face         decimal(12, 2) COMMENT '面单费公斤段30++',
    update_time                string COMMENT '更新时间',
    date_time                  date COMMENT 't-1日期',
    id                         string COMMENT 'id',
    dimension_str              string COMMENT 'network_id,send_financial_center_id,receiver_province_id拼接'
)
    COMMENT '临时表财务一口价-网点-签收(日)'
    PARTITIONED BY (
        dt string COMMENT '签收日期')
    STORED AS avro
    LOCATION
        '/dw/hive/spmi_dm.db/external/dm_composite_bill_network_sign_daliy_summary_dt'
